/**
 * e3db Fast database library for Grails
 * Copyright (C) 2009-2010 Collegeman.net, LLC
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along
 * with this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 */

import grails.test.*
import net.collegeman.grails.e3db.*
import org.springframework.jdbc.datasource.*

class E3dbTests extends GrailsUnitTestCase {
    
	private static String dbVersion = '5.0.51b'

	static {
		DB.setDefaultDataSource(new SimpleDriverDataSource(
			new com.mysql.jdbc.Driver(),
			"jdbc:mysql://localhost/e3db",
			"root",
			"password"
		))
	}

	protected void setUp() {
		DB.exec("DROP TABLE IF EXISTS test");
		DB.exec("CREATE TABLE test (name VARCHAR(128), color VARCHAR(10), featured TINYINT(1))")
		DB.batch("INSERT INTO test (name, color, featured) VALUES (?, ?, ?)", [
			['Aaron', 'red', 1],
			['Karla', 'blue', 1],
			['Izzy', 'green', 0],
			['Maria', 'black', 1]
		]);
	}

    protected void tearDown() {
		DB.exec("DROP TABLE IF EXISTS test");
    }

    void testGetSql() {
		assert dbVersion == DB.get("SELECT @@VERSION")
		assert 'foo' != DB.get("SELECT @@VERSION")
    }

	void testGetClosure() {
		assert dbVersion == DB.get {
			"SELECT @@VERSION"
		}
		
		assert dbVersion == DB.get {
			sql("SELECT @@VERSION")
		}
		
		assert dbVersion == DB.get {
			sql("SELECT")
			sql("@@VERSION")
		}		
	}
	
	void testGetInt() {
		assert 4 == DB.get("SELECT COUNT(*) FROM test")
		assert 4 == DB.getInt("SELECT COUNT(*) FROM test")
		assert new Integer(4) == DB.getInt("SELECT COUNT(*) FROM test")
		assert !new Long(4).equals(DB.getInt("SELECT COUNT(*) FROM test"))
	}
	
	void testGetLong() {
		assert 4 == DB.get("SELECT COUNT(*) FROM test")
		assert 4 == DB.getLong("SELECT COUNT(*) FROM test")
		assert new Long(4) == DB.getLong("SELECT COUNT(*) FROM test")
		assert !new Integer(4).equals(DB.getLong("SELECT COUNT(*) FROM test"))
	}
	
	void testRows() {
		def tests = [ 'Aaron', 'Karla', 'Izzy', 'Maria' ]
		
		DB.rows("SELECT * FROM test").eachWithIndex { test, i ->
			assert tests[i] == test.name
		}
		
		DB.rows("SELECT * FROM test").eachWithIndex { test, i ->
			assert tests[i] != test.color
		}
	}
	
	static def params = [ go: true ]
	
	void testDelegation() {
		assert dbVersion == DB.get {
			if (params.go)
				sql("SELECT @@VERSION")
			else
				sql("SELECT 'foo'")
		}
	}
	
	void testInjunctions() {
		assert 2 == DB.get {
			sql("SELECT count(name) FROM test WHERE")
			or {
				sql("color = ?", 'red')
				sql("color = ?", 'blue')
			}
		}
		
		assert 1 == DB.get {
			sql("SELECT count(name) FROM test WHERE")
			or {
				sql("color = ?", 'red')
				sql("name = ?", 'Aaron')
			}
		}
	}
	
	void testWhere() {
		assert 1 == DB.get {
			sql("SELECT COUNT(*) FROM test")
			where {
				sql('color = ?', 'red')
			}
		}
		
		assert 2 == DB.get {
			sql("SELECT COUNT(*) FROM test")
			where {
				or {
					['red', 'blue'].each {
						sql('color = ?', it)
					}
				}
			}
		}
	}
	
	void testExists() {
		assert 1 == DB.get {
			sql("SELECT")
			exists {
				sql("SELECT name FROM test WHERE color = ?", 'red')
			}
		}
	}
	
	void testNamedParameters() {
		assert 'Aaron' == DB.get {
			sql("SELECT name FROM test WHERE color = :color", [color: 'red'])
		}
		
		assert 2 == DB.get {
			sql("SELECT COUNT(*) FROM test WHERE color IN (:color)", [color: ['red', 'blue']])
		}
	}
	
	void testBuffer() {
		assert "SELECT * FROM test WHERE color = ?" == DB.buffer {
			sql("SELECT * FROM test WHERE color = ?", 'red')
		}.getSql();
	}
	

}
